Imports System.Data.SqlClient

Friend Class DBScanProcedure
    Inherits DBBase

    Private moServer As SQLAutoDocLib.Server
    Private moDatabase As SQLAutoDocLib.Database

    Public Event ItemScanStart(ByVal lServerID As Long, ByVal lDatabaseID As Long, ByVal lSessionID As Long, ByVal lSProcID As Long)
    Public Event ItemScanComplete(ByVal lSessionID As Long, ByVal lSProcID As Long, ByVal oEndDate As Date)

    Public Sub New( _
                ByVal oServer As SQLAutoDocLib.Server, _
                ByVal oDatabase As SQLAutoDocLib.Database)

        moServer = oServer
        moDatabase = oDatabase
    End Sub

    Public Function Scan(ByVal lSessionID As Long) As Boolean
        Dim bChangesFound As Boolean = False

        Dim oFactory As New SProc_Factory
        Dim oAllSProcs As Collection = oFactory.GetAllSProcsForDatabase(moDatabase.ID)

        For Each oSproc As SQLAutoDocLib.SProc In oFactory.GetAllSProcsForDatabase(moDatabase.ID)
            oSproc.IsDeleted = True
            oSproc.Save()
        Next

        Dim iAlteredSProcs As Integer = 0
        Dim bProcsAddedOrUpdated As Boolean = False
        RefreshSProcsForDatabase(lsessionid, oAllSProcs, iAlteredSProcs)
        If iAlteredSProcs > 0 Then bProcsAddedOrUpdated = True

        If bProcsAddedOrUpdated = False Then
            '---Were any sprocs deleted?
            For Each oSproc As SQLAutoDocLib.SProc In oFactory.GetAllSProcsForDatabase(moDatabase.ID)
                If oSproc.IsDeleted = True Then
                    oSproc.DatabaseRevID = moDatabase.RevID
                    oSproc.Save(bForceAdd:=True)
                    oSproc.Save()
                End If
            Next
        Else
            bChangesFound = True
        End If

        Return bChangesFound
    End Function

    Private Sub RefreshSProcsForDatabase( _
                        ByVal lSessionID As Long, _
                        ByRef oAllSProcs As Collection, _
                        ByRef iAlteredSProcs As Integer)

        Dim sSQL = "select distinct"
        sSQL = sSQL & "     a.id,"
        sSQL = sSQL & "     b.name 'SProcOwner',"
        sSQL = sSQL & "     a.name 'SProcName'"
        sSQL = sSQL & " from "
        sSQL = sSQL & " dbo.sysobjects a"
        sSQL = sSQL & " join dbo.sysusers b on b.uid=a.uid"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     a.type = 'p'"
        sSQL = sSQL & "     and a.category!=2"

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL, moDatabase.DecryptedConnectionString)

        Dim oLoginFactory As New Login_Factory
        Dim oSProcFactory As New SProc_Factory

        For Each oRow As DataRow In oData.Rows
            Dim oLogin As SQLAutoDocLib.Login = oLoginFactory.GetLoginByName(moDatabase.ID, oRow.Item("SProcOwner"))
            Dim oSProc As SQLAutoDocLib.SProc = oSProcFactory.GetSProcByName(moDatabase.ID, oLogin.ID, oRow.Item("SProcName"))

            If oSProc Is Nothing Then
                '---Found a new proc
                oSProc = New SQLAutoDocLib.SProc
                With oSProc
                    .Name = oRow.Item("SProcName")
                    .OwnerID = oLogin.ID
                    .OID = oRow.Item("id")
                    .DatabaseID = moDatabase.ID
                    .DatabaseRevID = moDatabase.RevID
                End With

                iAlteredSProcs = iAlteredSProcs + 1
            End If

            If oAllSProcs.Contains(CStr(oSProc.ID)) Then
                oAllSProcs(CStr(oSProc.ID)).IsDeleted = False
            End If

            oSProc.IsDeleted = False

            oSProc.Save()

            RaiseEvent ItemScanStart(moDatabase.ServerID, moDatabase.ID, lsessionid, oSProc.ID)

            oSProc.DatabaseRevID = moDatabase.RevID
            Dim bProcUpdated As Boolean = oSProc.Scan()
            If bProcUpdated = True Then
                iAlteredSProcs = iAlteredSProcs + 1
            End If

            RaiseEvent ItemScanComplete(lSessionID, oSProc.ID, Now)

        Next
    End Sub
End Class
